package com.tallysystem.daoimpl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.ResultSet;

import com.tallysystem.dao.TallysDao;
import com.tallysystem.entity.Tallys;
import com.tallysystem.entity.User;
import com.test.util.JdbcUtil;

public class TallysDaoImpl implements TallysDao{
	
	//添加
	@Override
	public void addtallys(Tallys tallys) {
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "insert into tallyform(debit,spendtype,remarks,spendtime,clock) values(?,?,?,?,?) ";
			// 3. 创建Statement
			PreparedStatement stm = conn.prepareStatement(sql);
			stm.setDouble(1, tallys.getDebit());
			stm.setString(2, tallys.getSpendtype());
			stm.setString(3, tallys.getRemarks());
			stm.setDate(4, tallys.getSpendtime());
			stm.setDate(5, tallys.getClock());
//			stm.setInt(6, tallys.getTid());
			// 4. 执行SQL语句
			int res = stm.executeUpdate();
			
			JdbcUtil.free(null, stm, conn);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//查询所有
	@Override
	public List<Tallys> getAllTallys() {
		// 1. 加载驱动
				try {
					// 2. 建立连接
					Connection con = JdbcUtil.getConnection();
					String sql = "select * from `tallyform` ";
					// 3. 创建Statement
					PreparedStatement stm = con.prepareStatement(sql);
					// 4. 执行SQL语句
					ResultSet res = stm.executeQuery();
					// 5. 处理结果
					List<Tallys> tallyslist = new ArrayList<>();
					while (res.next()) {
						
						Double debit = res.getDouble("debit");
						String spendtype = res.getString("spendtype");
						String remarks = res.getString("remarks");
						Date spendtime = res.getDate("spendtime");				
						Date clock = res.getDate("clock");
						int tid = res.getInt("tid");
						tallyslist.add(new Tallys(debit, spendtype,remarks,spendtime, clock,tid,null,null));
					}
					// 6. 释放资源
					JdbcUtil.free(res, stm, con);
					
					return tallyslist;
				} 
				catch (SQLException e) {
					e.printStackTrace();
				}

				return null;

	}
	
	//单一查询
	@Override
	public Tallys findBytid(int tid) {
		Tallys tallyOneText = null;
		try {
			Connection con = JdbcUtil.getConnection();
			String sql = "select * from tallyform where tid =? ";
			// 3. 创建Statement
			PreparedStatement stm = con.prepareStatement(sql);
			stm.setInt(1, tid);
			// 4. 执行SQL语句
			ResultSet res = stm.executeQuery();
			// 5. 处理结果
			while (res.next()) {
				tallyOneText = new Tallys();
				tallyOneText.setDebit(res.getDouble("debit"));
				tallyOneText.setSpendtype(res.getString("spendtype"));
				tallyOneText.setRemarks(res.getString("remarks"));
				tallyOneText.setSpendtime(res.getDate("spendtime"));
				tallyOneText.setClock(res.getDate("clock"));
				tallyOneText.setTid(res.getInt("tid"));
			}
			JdbcUtil.free(res, stm, con);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tallyOneText;
	}
	
	//删除
	@Override
	public int deletetallys(int tid) {
		int res = 0;
		// 1. 加载驱动
				try {
					// 2. 建立连接
					Connection con = JdbcUtil.getConnection();
					String sql = "delete from tallyform where tid=?";
					// 3. 创建Statement
					// 4. 执行SQL语句
					PreparedStatement stm = con.prepareStatement(sql);
					//5.处理结果
					stm.setInt(1, tid);
					// 4. 执行SQL语句
					res = stm.executeUpdate();	
					JdbcUtil.free(null, stm, con);
				} 
				catch (SQLException e) {
					e.printStackTrace();
				}
				return res;
				
	}

	//修改功能
	@Override
	public int updatetallys(Tallys tallys) {
		int res = 0;
		// 1. 加载驱动
				try {
					// 2. 建立连接
					Connection con = JdbcUtil.getConnection();
					String sql = "update tallyform set debit=?,spendtype=?,remarks=?,spendtime=?,clock=?,tid=?,updateReason=? where tid=?";
					// 3. 创建Statement
					// 4. 执行SQL语句
					PreparedStatement stm = con.prepareStatement(sql);
					//5.处理结果
					stm.setDouble(1, tallys.getDebit());
					stm.setString(2, tallys.getSpendtype());
					stm.setString(3, tallys.getRemarks());
					stm.setDate(4, tallys.getSpendtime());
					stm.setDate(5, tallys.getClock());
					stm.setInt(6, tallys.getTid());
					stm.setString(7, tallys.getUpdateReason());
					stm.setInt(8, tallys.getTid());
					// 4. 执行SQL语句
					res = stm.executeUpdate();	
					JdbcUtil.free(null, stm, con);
				} 
				catch (SQLException e) {
					e.printStackTrace();
				}
				return res;
				
	}

	
	
	//分页
	@Override
	public List<Tallys> queryPage(int pos, int page) {
		// 1. 加载驱动
		try {
			// 2. 建立连接
			Connection con = JdbcUtil.getConnection();
			String sql = "select * from `tallyform` limit ?,?";
			// 3. 创建Statement
			PreparedStatement stm = con.prepareStatement(sql);
			stm.setInt(1, pos);
			stm.setInt(2, page);
			// 4. 执行SQL语句
			ResultSet res = stm.executeQuery();
			// 5. 处理结果
			List<Tallys> pagelists = new ArrayList<>();
			while (res.next()) {
				Double debit = res.getDouble("debit");
				String spendtype = res.getString("spendtype");
				String remarks = res.getString("remarks");
				Date spendtime = res.getDate("spendtime");				
				Date clock = res.getDate("clock");
				int tid = res.getInt("tid");
				pagelists.add(new Tallys(debit, spendtype,remarks,spendtime, clock,tid,null,null));
			}
			// 6. 释放资源
			JdbcUtil.free(res, stm, con);

			return pagelists;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	@Override
	public int count() {
		int result = 0;
		// 1. 加载驱动
		try {
			// 2. 建立连接
			Connection con = JdbcUtil.getConnection();
			String sql = "select count(*) from tallyform";
			// 3. 创建Statement
			PreparedStatement stm = con.prepareStatement(sql);
			// 4. 执行SQL语句
			ResultSet res = stm.executeQuery();
			// 5. 处理结果
			while (res.next()) {
				result = res.getInt(1);
			}
			// 6. 释放资源
			JdbcUtil.free(res, stm, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}

	
	//模糊查询
	@Override
	public List<Tallys> findByPageAndFuzzy(int pos, int page, StringBuffer condition) {
		// 1. 加载驱动
				try {
					// 2. 建立连接
					Connection con = JdbcUtil.getConnection();
					String sql = "select * from tallyform where" +condition+ " limit ?,?";
					// 3. 创建Statement
					PreparedStatement stm = con.prepareStatement(sql);
					stm.setInt(1, pos);
					stm.setInt(2, page);
					// 4. 执行SQL语句
					ResultSet res = stm.executeQuery();
					// 5. 处理结果
					List<Tallys> pageAndFuzzyLists = new ArrayList<>();
					while (res.next()) {
						Double debit = res.getDouble("debit");
						String spendtype = res.getString("spendtype");
						String remarks = res.getString("remarks");
						Date spendtime = res.getDate("spendtime");				
						Date clock = res.getDate("clock");
						int tid = res.getInt("tid");
						pageAndFuzzyLists.add(new Tallys(debit, spendtype,remarks,spendtime, clock,tid,null,null));
					}
					// 6. 释放资源
					JdbcUtil.free(res, stm, con);

					return pageAndFuzzyLists;
				} catch (SQLException e) {
					e.printStackTrace();
				}

				return null;
	}

}
